﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Linq;
using System.Web;

namespace KursnaListaService.Models
{
    public class Repository : IRepository
    {
        private readonly string _connectionString;

        public Repository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public bool Update(KursnaListaZaDan kursnaListaZaDan)
        {
            try
            {
                using (var connection = new SqlCeConnection(_connectionString))
                {
                    connection.Open();
                    SqlCeTransaction transaction = null;
                    try
                    {
                        transaction = connection.BeginTransaction();

                        InsertKursnaListaZaDan(connection, transaction, kursnaListaZaDan);
                        
                        transaction.Commit();
                    }
                    catch (Exception)
                    {
                        if(transaction!=null)
                            transaction.Rollback();
                        return false;
                    }                    
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        private List<int> GetSifreValuta(SqlCeConnection connection)
        {
            var result = new List<int>();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT Sifra FROM Valute";

            using (var rdr = command.ExecuteReader())
            {
                while (rdr.Read())
                {
                    result.Add(rdr.GetInt32(0));
                }
            }
            
            return result;
        }

        private void InsertKursnaListaZaDan(SqlCeConnection connection, SqlCeTransaction transaction, KursnaListaZaDan kursnaListaZaDan)
        {
            var sifreValuta = GetSifreValuta(connection);

            var command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "INSERT INTO Lista (Broj,Datum) VALUES(@Broj,@Datum)";
            command.Parameters.AddWithValue("Broj", kursnaListaZaDan.Broj);
            command.Parameters.AddWithValue("Datum", kursnaListaZaDan.Datum);
            command.ExecuteNonQuery();

            command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "SELECT @@IDENTITY";
            var id =  Convert.ToInt32(command.ExecuteScalar());
            foreach (var stavkaKursneListe in kursnaListaZaDan.SrednjiKurs)
            {
                if (!sifreValuta.Contains(stavkaKursneListe.SifraValute))
                {
                    InsertValuta(connection, transaction, stavkaKursneListe.SifraValute, stavkaKursneListe.NazivZemlje, stavkaKursneListe.OznakaValute);
                    sifreValuta.Add(stavkaKursneListe.SifraValute);
                }
                InsertSrednjiKurs(connection, transaction, id, stavkaKursneListe.SifraValute, stavkaKursneListe.VaziZa, stavkaKursneListe.SrednjiKurs);
            }
            foreach (var stavkaKursneListe in kursnaListaZaDan.ZaDevize)
            {
                if (!sifreValuta.Contains(stavkaKursneListe.SifraValute))
                {
                    InsertValuta(connection, transaction, stavkaKursneListe.SifraValute, stavkaKursneListe.NazivZemlje, stavkaKursneListe.OznakaValute);
                    sifreValuta.Add(stavkaKursneListe.SifraValute);
                }
                InsertZaDevize(connection, transaction, id, stavkaKursneListe.SifraValute, stavkaKursneListe.VaziZa, stavkaKursneListe.KupovniKurs, stavkaKursneListe.ProdajniKurs);
            }
            foreach (var stavkaKursneListe in kursnaListaZaDan.ZaEfektivniStraniNovac)
            {
                if (!sifreValuta.Contains(stavkaKursneListe.SifraValute))
                {
                    InsertValuta(connection, transaction, stavkaKursneListe.SifraValute, stavkaKursneListe.NazivZemlje, stavkaKursneListe.OznakaValute);
                    sifreValuta.Add(stavkaKursneListe.SifraValute);
                }
                InsertZaEfektivniStraniNovac(connection, transaction, id, stavkaKursneListe.SifraValute, stavkaKursneListe.VaziZa, stavkaKursneListe.KupovniKurs, stavkaKursneListe.ProdajniKurs);
            }
        }

        private void InsertValuta(SqlCeConnection connection, SqlCeTransaction transaction, int sifra, string nazivZemlje, string oznaka)
        {
            var command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "INSERT INTO Valute (Sifra,NazivZemlje,Oznaka) VALUES(@Sifra,@NazivZemlje,@Oznaka)";
            command.Parameters.AddWithValue("Sifra", sifra);
            command.Parameters.AddWithValue("NazivZemlje", nazivZemlje);
            command.Parameters.AddWithValue("Oznaka", oznaka);
            command.ExecuteNonQuery();
        }

        private void InsertSrednjiKurs(SqlCeConnection connection, SqlCeTransaction transaction, int listaID, int sifraValute, int vaziZa, decimal srednjiKurs)
        {
            var command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "INSERT INTO SrednjiKurs (ListaID,SifraValute,VaziZa,SrednjiKurs) VALUES(@ListaID,@SifraValute,@VaziZa,@SrednjiKurs)";
            command.Parameters.AddWithValue("ListaID", listaID);
            command.Parameters.AddWithValue("SifraValute", sifraValute);
            command.Parameters.AddWithValue("VaziZa", vaziZa);
            command.Parameters.AddWithValue("SrednjiKurs", srednjiKurs);
            command.ExecuteNonQuery();
        }

        private void InsertZaDevize(SqlCeConnection connection, SqlCeTransaction transaction, int listaID, int sifraValute, int vaziZa, decimal kupovniKurs, decimal prodajniKurs)
        {
            var command = connection.CreateCommand();
            command.Transaction = transaction; 
            command.CommandText = "INSERT INTO ZaDevize (ListaID,SifraValute,VaziZa,KupovniKurs,ProdajniKurs) VALUES(@ListaID,@SifraValute,@VaziZa,@KupovniKurs,@ProdajniKurs)";
            command.Parameters.AddWithValue("ListaID", listaID);
            command.Parameters.AddWithValue("SifraValute", sifraValute);
            command.Parameters.AddWithValue("VaziZa", vaziZa);
            command.Parameters.AddWithValue("KupovniKurs", kupovniKurs);
            command.Parameters.AddWithValue("ProdajniKurs", prodajniKurs);
            command.ExecuteNonQuery();
        }

        private void InsertZaEfektivniStraniNovac(SqlCeConnection connection, SqlCeTransaction transaction, int listaID, int sifraValute, int vaziZa, decimal kupovniKurs, decimal prodajniKurs)
        {
            var command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "INSERT INTO ZaEfektivniStraniNovac (ListaID,SifraValute,VaziZa,KupovniKurs,ProdajniKurs) VALUES(@ListaID,@SifraValute,@VaziZa,@KupovniKurs,@ProdajniKurs)";
            command.Parameters.AddWithValue("ListaID", listaID);
            command.Parameters.AddWithValue("SifraValute", sifraValute);
            command.Parameters.AddWithValue("VaziZa", vaziZa);
            command.Parameters.AddWithValue("KupovniKurs", kupovniKurs);
            command.Parameters.AddWithValue("ProdajniKurs", prodajniKurs);
            command.ExecuteNonQuery();
        }

        public DateTime? GetNajnovijiDan()
        {
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                return GetNajnovijiDan(connection);
            }
        }

        private DateTime? GetNajnovijiDan(SqlCeConnection connection)
        {
            var command = connection.CreateCommand();
            command.CommandText = "SELECT TOP 1 Datum FROM Lista ORDER BY Datum DESC";

            using (var rdr = command.ExecuteReader())
            {
                if (rdr.Read())
                {
                    return rdr.GetDateTime(0);
                }
                return null;
            }
        }

        public IEnumerable<KursnaListaZaDan> GetKursnaListaZaNDana(int n)
        {
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var result = GetKursneListeZaDan(connection, DateTime.Now.Date.AddDays(1-n));
                var after = result.First().ID;
                var valute = GetValute(connection);
                var kursneListeZaDan = result.ToDictionary(k => k.ID);
                FillSrednjiKurs(connection, kursneListeZaDan, valute, after);
                FillZaDevize(connection, kursneListeZaDan, valute, after);
                FillZaEfektivniStraniNovac(connection, kursneListeZaDan, valute, after);

                return result;
            }
        }

        private Dictionary<int,Valuta> GetValute(SqlCeConnection connection)
        {
            var result = new Dictionary<int, Valuta>();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT Sifra,NazivZemlje,Oznaka FROM Valute";

            using (var rdr = command.ExecuteReader())
            {
                while (rdr.Read())
                {
                    var valuta = new Valuta { 
                        SifraValute = rdr.GetInt32(0),
                        NazivZemlje = rdr.GetString(1),
                        OznakaValute = rdr.GetString(2)
                    };
                    result.Add(valuta.SifraValute, valuta);
                }
            }

            return result;
        }

        private List<KursnaListaZaDan> GetKursneListeZaDan(SqlCeConnection connection, DateTime after)
        {
            var result = new List<KursnaListaZaDan>();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT ID,Broj,Datum FROM Lista WHERE Datum >= @After ORDER BY ID ASC";
            command.Parameters.AddWithValue("After", after);

            using (var rdr = command.ExecuteReader())
            {
                while (rdr.Read())
                {
                    var kursnaListaZaDan = new KursnaListaZaDan
                    {
                        ID = rdr.GetInt32(0),
                        Broj = rdr.GetInt32(1),
                        Datum = rdr.GetDateTime(2),
                        SrednjiKurs = new List<StavkaKursneListe>(),
                        ZaDevize = new List<StavkaKursneListe>(),
                        ZaEfektivniStraniNovac = new List<StavkaKursneListe>(),
                    };
                    result.Add(kursnaListaZaDan);
                }
            }

            return result;
        }

        private void FillSrednjiKurs(SqlCeConnection connection, Dictionary<int, KursnaListaZaDan> kursneListeZaDan, Dictionary<int, Valuta> valute, int after)
        {
            var command = connection.CreateCommand();
            command.CommandText = "SELECT ListaID,SifraValute,VaziZa,SrednjiKurs FROM SrednjiKurs WHERE ListaID >= @After ORDER BY ID ASC";
            command.Parameters.AddWithValue("After", after);

            using (var rdr = command.ExecuteReader())
            {
                while (rdr.Read())
                {
                    var listaID = rdr.GetInt32(0);
                    var sifraValute = rdr.GetInt32(1);
                    var kursnaListaZaDan = kursneListeZaDan[listaID];
                    var valuta = valute[sifraValute];
                    var item = new StavkaKursneListe
                    {
                        VaziZa = rdr.GetInt32(2),
                        SrednjiKurs = rdr.GetDecimal(3),
                        SifraValute = sifraValute,
                        NazivZemlje = valuta.NazivZemlje,
                        OznakaValute = valuta.OznakaValute,
                    };
                    kursnaListaZaDan.SrednjiKurs.Add(item);
                }
            }
        }

        private void FillZaDevize(SqlCeConnection connection, Dictionary<int, KursnaListaZaDan> kursneListeZaDan, Dictionary<int, Valuta> valute, int after)
        {
            var command = connection.CreateCommand();
            command.CommandText = "SELECT ListaID,SifraValute,VaziZa,KupovniKurs,ProdajniKurs FROM ZaDevize WHERE ListaID >= @After ORDER BY ID ASC";
            command.Parameters.AddWithValue("After", after);

            using (var rdr = command.ExecuteReader())
            {
                while (rdr.Read())
                {
                    var listaID = rdr.GetInt32(0);
                    var sifraValute = rdr.GetInt32(1);
                    var kursnaListaZaDan = kursneListeZaDan[listaID];
                    var valuta = valute[sifraValute];
                    var item = new StavkaKursneListe
                    {
                        VaziZa = rdr.GetInt32(2),
                        KupovniKurs = rdr.GetDecimal(3),
                        ProdajniKurs = rdr.GetDecimal(4),
                        SifraValute = sifraValute,
                        NazivZemlje = valuta.NazivZemlje,
                        OznakaValute = valuta.OznakaValute,
                    };
                    kursnaListaZaDan.ZaDevize.Add(item);
                }
            }
        }

        private void FillZaEfektivniStraniNovac(SqlCeConnection connection, Dictionary<int, KursnaListaZaDan> kursneListeZaDan, Dictionary<int, Valuta> valute, int after)
        {
            var command = connection.CreateCommand();
            command.CommandText = "SELECT ListaID,SifraValute,VaziZa,KupovniKurs,ProdajniKurs FROM ZaEfektivniStraniNovac WHERE ListaID >= @After ORDER BY ID ASC";
            command.Parameters.AddWithValue("After", after);

            using (var rdr = command.ExecuteReader())
            {
                while (rdr.Read())
                {
                    var listaID = rdr.GetInt32(0);
                    var sifraValute = rdr.GetInt32(1);
                    var kursnaListaZaDan = kursneListeZaDan[listaID];
                    var valuta = valute[sifraValute];
                    var item = new StavkaKursneListe
                    {
                        VaziZa = rdr.GetInt32(2),
                        KupovniKurs = rdr.GetDecimal(3),
                        ProdajniKurs = rdr.GetDecimal(4),
                        SifraValute = sifraValute,
                        NazivZemlje = valuta.NazivZemlje,
                        OznakaValute = valuta.OznakaValute,
                    };
                    kursnaListaZaDan.ZaEfektivniStraniNovac.Add(item);
                }
            }
        }

    }
}